import JDBCUtils.JDBCUtils;
import org.junit.jupiter.api.Test;
import pojo.Examstudent;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Scanner;

/**
 * 练习题2： 向examstudent,表中添加一条记录 添加四六级成绩
 *
 * Type:
 * IDCard:
 * ExamCard:
 * StudentName:
 * Location:
 * Grade:
 */
public class ExerTest02 {
    //问题1:插入一个新的student 信息
    @Test
    public  void  testInsert(){
        try {
            Scanner scanner = new Scanner(System.in);
            System.out.println("四级/六级:");
            int type=scanner.nextInt();
            System.out.println("身份证号:");
            String IDCard=scanner.next();
            System.out.println("准考证号:");
            String examCard=scanner.next();
            System.out.println("学生姓名:");
            String studentName=scanner.next();
            System.out.println("所在城市:");
            String location=scanner.next();
            System.out.println("学生成绩:");
            int  grade=scanner.nextInt();
            String sql="insert into examstudent(Type,IDCard,ExamCard,StudentName,Location,Grade) values(?,?,?,?,?,?)";
            boolean update = JDBCUtils.update(sql, type, IDCard, examCard, studentName, location, grade);
            if(update){
                System.out.println("添加成功");
            }else{
                System.out.println("添加失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //问题2:根据身份证号或者准考证号查询学生成绩
    @Test
    public  void queryWithIDCardOrExamCard(){
        System.out.println("请选择您要输入的类型:");
        System.out.println("a.准考证号");
        System.out.println("b.身份证号");
        Scanner scanner=new Scanner(System.in);
        String selection = scanner.next();
        if("a".equalsIgnoreCase(selection)){//if(select.equalsIgnoreCase("a"))
            System.out.println("请输入准考证号:");
            String examCard = scanner.next();
            String sql="select FlowID flowId ,Type type,IDCard idCard,ExamCard examCard,StudentName studentName,Location location,Grade grade from examstudent where examCard=?";
            Examstudent examstudent = getInstance(Examstudent.class, sql, examCard);
            if(examstudent!=null){
                System.out.println(examstudent);
            }else {
                System.out.println("查无此人");
            }
        }else if("b".equalsIgnoreCase(selection)){
            System.out.println("请输入身份证号:");
            String IDCard = scanner.next();
            String sql="select FlowID flowId ,Type type,IDCard idCard,ExamCard examCard,StudentName studentName,Location location,Grade grade from examstudent where IDCard=?";
            Examstudent examstudent = getInstance(Examstudent.class, sql, IDCard);
            if(examstudent!=null){
                System.out.println(examstudent);
            }else {
                System.out.println("查无此人");
            }
        }else {
            System.out.println("您的输入有误请重新进入程序");
        }
    }
    //问题3:删除指定学生的信息
    @Test
    public  void  testDeleteByExamCard(){
        try {
            Scanner scanner=new Scanner(System.in);
            System.out.println("请输入学生的考号");
            String examCard=scanner.next();
            String sql="select FlowID flowId ,Type type,IDCard idCard,ExamCard examCard,StudentName studentName,Location location,Grade grade from examstudent where examCard=?";
            //查询指定准考证号的学生
            Examstudent student = getInstance(Examstudent.class, sql, examCard);
            if(student==null){
                System.out.println("查无此人,请重新输入");
            }else {
                String delSql="delete from examstudent where examCard=?";
                boolean update = JDBCUtils.update(delSql,examCard);
                if(update){
                    System.out.println("删除成功");
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 通用查询单个信息
     * @param clazz
     * @param sql
     * @param args
     * @param <T>
     * @return
     */
    public <T> T getInstance(Class<T> clazz,String sql, Object... args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();

            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }

            rs = ps.executeQuery();
            // 获取结果集的元数据 :ResultSetMetaData
            ResultSetMetaData rsmd = rs.getMetaData();
            // 通过ResultSetMetaData获取结果集中的列数
            int columnCount = rsmd.getColumnCount();

            if (rs.next()) {
                T t = clazz.newInstance();
                // 处理结果集一行数据中的每一个列
                for (int i = 0; i < columnCount; i++) {
                    // 获取列值
                    Object columValue = rs.getObject(i + 1);

                    // 获取每个列的列名
                    // String columnName = rsmd.getColumnName(i + 1);
                    String columnLabel = rsmd.getColumnLabel(i + 1);

                    // 给t对象指定的columnName属性，赋值为columValue：通过反射
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columValue);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps, rs);

        }

        return null;
    }

}
